#!/bin/bash
# MySQL事务日志(binlog)写入阻塞（binlog满）自动处理脚本
FIX_CODE_ID="binlog"

# 配置参数 - 根据实际环境修改
MYSQL_USER="root"                  # MySQL管理员用户
MYSQL_PASSWORD="your_mysql_password"# MySQL密码
MYSQL_HOST="localhost"             # MySQL主机地址
MYSQL_PORT=3306                    # MySQL端口
BINLOG_DIR="/var/lib/mysql"        # binlog存储目录
DISK_USAGE_THRESHOLD=85            # 磁盘使用率阈值(%)
BINLOG_RETENTION_DAYS=7            # binlog保留天数
EXPAND_SIZE="10G"                  # 扩展空间大小时的增量
LOG_FILE="/var/log/binlog_management.log"
SAFE_BINLOG_AGE=3600               # 安全清理的binlog最小存在时间(秒)

# 日志函数
log() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_FILE"
}

# 执行MySQL命令的封装函数
mysql_cmd() {
    local query=$1
    mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_HOST" -P"$MYSQL_PORT" -N -B -e "$query" 2>/dev/null
}

# 检查MySQL服务状态
check_mysql_alive() {
    if mysql_cmd "SELECT 1" >/dev/null; then
        return 0
    else
        log "MySQL服务连接失败"
        return 1
    fi
}

# 获取binlog存储目录的磁盘使用率
get_binlog_disk_usage() {
    df -P "$BINLOG_DIR" | tail -1 | awk '{print $5}' | sed 's/%//'
}

# 检查是否存在binlog写入阻塞
check_binlog_block() {
    # 检查错误日志中是否有binlog写入失败记录
    local error_log=$(mysql_cmd "SHOW VARIABLES LIKE 'log_error'" | awk '{print $2}')
    
    if [ -f "$error_log" ]; then
        if grep -q "Failed to write to binary log" "$error_log" || grep -q "Disk full writing binlog" "$error_log"; then
            log "检测到binlog写入阻塞错误"
            return 0
        fi
    fi
    
    # 结合磁盘使用率判断
    local disk_usage=$(get_binlog_disk_usage)
    if [ $disk_usage -ge $DISK_USAGE_THRESHOLD ]; then
        log "binlog所在磁盘使用率过高($disk_usage%)，存在写入阻塞风险"
        return 0
    fi
    
    return 1
}

# 获取可清理的binlog列表（基于保留天数）
get_purgeable_binlogs() {
    # 获取当前正在使用的binlog文件
    local current_binlog=$(mysql_cmd "SHOW MASTER STATUS" | awk '{print $1}')
    
    if [ -z "$current_binlog" ]; then
        log "无法获取当前活跃的binlog文件"
        return 1
    fi
    
    log "当前活跃的binlog文件: $current_binlog"
    
    # 获取所有binlog文件列表
    local binlogs=$(mysql_cmd "SHOW BINARY LOGS" | awk 'NR>1 {print $1}')
    local purgeable=""
    
    # 筛选出可清理的binlog（非当前活跃且超过保留期）
    echo "$binlogs" | while read -r binlog; do
        if [ "$binlog" != "$current_binlog" ]; then
            # 检查binlog创建时间
            local binlog_path="$BINLOG_DIR/$binlog"
            if [ -f "$binlog_path" ]; then
                local create_time=$(stat -c %Y "$binlog_path")
                local current_time=$(date +%s)
                local age=$((current_time - create_time))
                
                if [ $age -gt $((BINLOG_RETENTION_DAYS * 86400)) ] && [ $age -gt $SAFE_BINLOG_AGE ]; then
                    purgeable="$purgeable $binlog"
                fi
            fi
        fi
    done
    
    echo "$purgeable"
}

# 清理过期binlog
purge_old_binlogs() {
    log "开始清理过期binlog文件"
    
    local purgeable=$(get_purgeable_binlogs)
    
    if [ -z "$purgeable" ]; then
        log "没有可清理的binlog文件"
        return 1
    fi
    
    # 获取最早的可清理binlog作为清理点
    local oldest_purgeable=$(echo "$purgeable" | tr ' ' '\n' | sort | head -1)
    
    if [ -n "$oldest_purgeable" ]; then
        log "清理到$oldest_purgeable之前的所有binlog"
        # 执行清理
        if mysql_cmd "PURGE BINARY LOGS TO '$oldest_purgeable'"; then
            log "binlog清理成功"
            return 0
        else
            log "binlog清理失败，尝试手动删除"
            # 备选方案：手动删除
            echo "$purgeable" | while read -r binlog; do
                rm -f "$BINLOG_DIR/$binlog"
                log "已手动删除binlog: $binlog"
            done
            # 刷新binlog索引
            mysql_cmd "FLUSH BINARY LOGS"
            return 0
        fi
    fi
    
    return 1
}

# 扩展binlog所在磁盘空间（LVM环境）
extend_disk_space() {
    log "尝试扩展binlog所在磁盘空间"
    
    # 检查是否为LVM环境
    local vg_name=$(df -P "$BINLOG_DIR" | tail -1 | awk '{print $1}' | cut -d'-' -f1-2)
    
    if [ -z "$vg_name" ] || ! vgs "$vg_name" >/dev/null 2>&1; then
        log "非LVM环境，无法自动扩展磁盘空间"
        return 1
    fi
    
    # 检查是否有可用的PV空间
    local free_space=$(vgs --noheadings -o vg_free "$vg_name" | awk '{print $1}')
    
    if [ -z "$free_space" ] || [ "$free_space" = "0" ]; then
        log "卷组$vg_name没有可用空间，无法扩展"
        return 1
    fi
    
    # 执行扩展
    local lv_path=$(df -P "$BINLOG_DIR" | tail -1 | awk '{print $1}')
    
    log "扩展逻辑卷$lv_path，增加$EXPAND_SIZE空间"
    lvextend -L +"$EXPAND_SIZE" "$lv_path" >/dev/null 2>&1
    
    # 扩展文件系统
    if grep -q "xfs" /proc/mounts | grep -q "$BINLOG_DIR"; then
        xfs_growfs "$BINLOG_DIR" >/dev/null 2>&1
    else
        resize2fs "$lv_path" >/dev/null 2>&1
    fi
    
    log "磁盘空间扩展完成"
    return 0
}

# 临时调整binlog配置（紧急情况下）
adjust_binlog_config() {
    log "调整binlog相关配置参数"
    
    # 临时减小binlog文件大小
    mysql_cmd "SET GLOBAL max_binlog_size = 104857600"  # 100M
    
    # 临时关闭binlog（仅在极端情况下使用）
    local disk_usage=$(get_binlog_disk_usage)
    if [ $disk_usage -ge 95 ]; then
        log "磁盘空间极度紧张，临时关闭binlog"
        mysql_cmd "SET GLOBAL sql_log_bin = OFF"
        # 记录此操作，提醒后续恢复
        log "!!! 已临时关闭binlog，需要手动恢复 !!!"
    fi
    
    return 0
}

# 主逻辑
main() {
    log "开始binlog日志状态检查"
    
    # 检查MySQL连接状态
    if ! check_mysql_alive; then
        log "MySQL服务不可用，尝试重启"
        systemctl restart mysql
        sleep 10
        if ! check_mysql_alive; then
            log "MySQL重启失败，需要人工干预"
            exit 1
        fi
    fi
    
    # 检查是否存在binlog写入阻塞
    if ! check_binlog_block; then
        log "binlog状态正常，无写入阻塞"
        exit 0
    fi
    
    log "检测到binlog写入阻塞风险，启动处理流程"
    
    # 步骤1: 清理过期binlog
    purge_old_binlogs
    
    # 检查清理效果
    local disk_usage_after_purge=$(get_binlog_disk_usage)
    log "清理后磁盘使用率: $disk_usage_after_purge%"
    
    if [ $disk_usage_after_purge -lt $DISK_USAGE_THRESHOLD ]; then
        log "binlog清理后空间充足，恢复正常"
        # 如之前关闭了binlog，尝试恢复
        mysql_cmd "SET GLOBAL sql_log_bin = ON"
        exit 0
    fi
    
    # 步骤2: 扩展磁盘空间
    extend_disk_space
    
    # 检查扩展效果
    local disk_usage_after_extend=$(get_binlog_disk_usage)
    log "扩展后磁盘使用率: $disk_usage_after_extend%"
    
    if [ $disk_usage_after_extend -lt $DISK_USAGE_THRESHOLD ]; then
        log "磁盘空间扩展成功，binlog写入恢复正常"
        # 如之前关闭了binlog，尝试恢复
        mysql_cmd "SET GLOBAL sql_log_bin = ON"
        exit 0
    fi
    
    # 步骤3: 临时调整配置（最后手段）
    adjust_binlog_config
    log "已执行紧急配置调整，缓解binlog写入压力"
    
    # 最终检查
    if check_binlog_block; then
        log "所有措施均已尝试，但binlog写入仍存在阻塞，需要人工干预"
        exit 1
    else
        log "紧急调整后binlog写入恢复正常"
        exit 0
    fi
}

# 执行主逻辑
main
    